package com.gack.business.dao;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.gack.business.vo.OrderItemVO;
import com.gack.business.vo.StoresVO;

/**
 * 
* @ClassName: StoresDao 
* @Description: TODO(这里用一句话描述这个类的作用) 
* @author (ZhangXinYu)  
* @date 2018年6月5日 下午5:01:11 
* @version V1.0
 */
@Repository
public class StoresDao {
	@Autowired
	private EntityManager entityManager;
	@Autowired
	private StoreFacityAndServiceDao storeFacityAndServiceDao;
	
	/**
	 * String id,String storeName,String storeAddress,Date starttime,Date endtime,String supportfacity,
			String storeUser,String storePhone,String storeLandline,String attention,String longitude,
			String latitude,String storep_hotourl,String onephotourl,String twophoneurl,String threephotourl,
			String fourphotourl,String fivephotourl,String sixphotourl,Integer storeArea,String storeService,
			Integer storeDeposit,Integer storePrice,Integer status,Date createtime
	 */
	public StoresVO getStoreInfo(String storeid){
		StoresVO storesVO = null;
//		String jpql = "select new com.gack.business.vo.StoresVO"
//				+ "("
//				+ "s.id,s.storeName,s.storeAddress,s.starttime,s.endtime,s.supportfacity,s.storeUser,"
//				+ "s.storePhone,s.storeLandline,s.attention,s.longitude,s.latitude,s.storep_hotourl,"
//				+ "s.onephotourl,s.twophoneurl,s.threephotourl,s.fourphotourl,s.fivephotourl,s.sixphotourl,"
//				+ "s.storeArea,s.storeService,s.storeDeposit,s.storePrice,s.status,s.createtime,"
//				+ "s.isUse"
//				+ ") "
//				+ " from VideoStores s "
//				+ " where s.id = :storeid";
//		Query query = entityManager.createQuery(jpql);
//		query.setParameter("storeid", storeid);
//		storesVO = (StoresVO)query.getSingleResult();
		String jpql = "select "
						+ " s.id,s.store_name,s.store_address,s.starttime,endtime,s.supportfacity,s.store_user,"
						+ " s.store_phone,s.store_landline,s.attention,s.longitude,s.latitude,s.storep_hotourl,"
						+ " s.onephotourl,s.twophoneurl,s.threephotourl,s.fourphotourl,s.fivephotourl,s.sixphotourl,"
						+ " s.store_area,s.store_service,s.store_deposit,s.store_price,"
							+ " case "
								+ " when o.status = 1 then 1"
								+ " when o.status = 4 then 2"
								+ " else 0"
							+ " end as `status`,"
						+ " s.createtime,s.is_use,s.province,s.city,s.area"
					+ " from stores s "
					+ " LEFT JOIN orders o on o.store_id = s.id and o.status not in ('2','3','5','6','7','8','9','10')"
					+ " where 1=1 and s.id = :storeid";
		Query query = entityManager.createNativeQuery(jpql,StoresVO.class);
		query.setParameter("storeid", storeid);
		storesVO = (StoresVO)query.getSingleResult();
		storesVO.setFacities(storeFacityAndServiceDao.getAllStoreFacities(storeid));
		storesVO.setServices(storeFacityAndServiceDao.getAllStoreServices(storeid));
		return storesVO;
	}
	
	/*
	 * 门店未结束使用的订单数量
	 */
	public long getUnEndUsedStoreOrderCount(String storeid){
		BigInteger count = new BigInteger("0");
		String jpql = "select count(o.id) as count from orders o "
				+ " where 1 = 1 "
				+ " and o.store_id = :storeid "
				+ " and o.status not in ('2','3','8','9','10')";
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("storeid", storeid);
		count = (BigInteger)query.getSingleResult();
		return count.intValue();
	}
	
	/*
	 * 门店未结束使用的订单数量
	 */
	public long getUsedStoreOrderCount(String storeid){
		BigInteger count = new BigInteger("0");
		String jpql = "select count(o.id) as count from orders o "
				+ " where 1 = 1 "
				+ " and o.store_id = :storeid "
				+ " and o.status in ('1','4')";
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("storeid", storeid);
		count = (BigInteger)query.getSingleResult();
		return count.intValue();
	}
	
	/*
	 * 查询门店收入情况列表
	 * type 0 时间段查询   1 前一周收入  2前一月收入 3前一季度收入 4前一年收入
	 */
	public List<StoreRevenuenobind> getStoreRevenues(Integer type,String province ,String city,String area,String beginTime,String endTime,Integer page,Integer size){
		List<StoreRevenuenobind> storeRevenuenobinds = null;
		String jpql = "SELECT "
				+ " s.id,s.store_name,SUM(IFNULL(o.amount,0)) as amount"
				+ " FROM orders o "
				+ " LEFT JOIN stores s ON o.store_id = s.id "
				+ " WHERE o.status not in ('1','2','3','4')"
					+ " and s.is_use <> 1";
		if(type == null){
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 0){
			if(beginTime != null && !beginTime.equals("")){
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
				try {
					Date d = format.parse(beginTime.replace("Z", " UTC"));
					format = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
					beginTime = format.format(d);
				} catch (ParseException e) {
					e.printStackTrace();
				}//注意是空格+UTC
				jpql +=" and o.created_time >= '"+beginTime+"'";
			}else if(endTime != null && !endTime.equals("")){
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
				try {
					Date d = format.parse(endTime.replace("Z", " UTC"));
					format = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
					endTime = format.format(d);
				} catch (ParseException e) {
					e.printStackTrace();
				}//注意是空格+UTC
				jpql +=" and o.created_time < '"+endTime+"'";
			}
		}else if(type == 1){
			//周
			jpql +=" and o.created_time >= TIMESTAMP(DATE(DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -1 WEEK)))";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 2){
			//月
			jpql +=" and o.created_time >= DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -1 MONTH)";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 3){
			//季度
			jpql +=" and o.created_time >= TIMESTAMP(DATE(DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -1 MONTH)))";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 4){
			//年
			jpql +=" and o.created_time >= TIMESTAMP(DATE(DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -3 MONTH)))";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}
		if(province != null && !province.equals("")){
			jpql += " and s.province like '%"+province+"%'";
		}
		if(city != null && !city.equals("")){
			jpql += " and s.city like '%"+city+"%'";
		}
		if(area != null && !area.equals("")){
			jpql += " and s.area like '%"+area+"%'";
		}
		jpql += " GROUP BY o.store_id"
				+ " ORDER BY s.createtime desc";
		Query query = entityManager.createNativeQuery(jpql,StoreRevenuenobind.class);
		if(page == null || size == null){
			storeRevenuenobinds = (List<StoreRevenuenobind>)query.getResultList();
		}else if(page == -1 || size == -1){
			storeRevenuenobinds = (List<StoreRevenuenobind>)query.getResultList();
		}else{
			storeRevenuenobinds = (List<StoreRevenuenobind>)query.setFirstResult(page*size).setMaxResults(size).getResultList();
		}
		return storeRevenuenobinds;
	}
	
	/*
	 * 查询门店总收入
	 * type null 无条件查询  0 时间段查询   1 前一周收入  2前一月收入 3前一季度收入 4前一年收入
	 */
	public long getTotalStoreRevenue(Integer type,String province ,String city,String area,String beginTime,String endTime){
		BigDecimal total = new BigDecimal("0");
		String jpql = "SELECT "
				+ " IFNULL(SUM(a.amount),0) as amount "
				+ " FROM ("
					+ " SELECT "
					+ " SUM(IFNULL(o.amount,0)) as amount"
					+ " FROM orders o "
					+ " LEFT JOIN stores s ON o.store_id = s.id "
					+ " WHERE o.status not in ('1','2','3','4') "
						+ " and s.is_use <> 1";
		if(type == null){
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 0){
			if(beginTime != null && !beginTime.equals("")){
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
				try {
					Date d = format.parse(beginTime.replace("Z", " UTC"));
					format = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
					beginTime = format.format(d);
				} catch (ParseException e) {
					e.printStackTrace();
				}//注意是空格+UTC
				jpql +=" and o.created_time >= '"+beginTime+"'";
			}else if(endTime != null && !endTime.equals("")){
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
				try {
					Date d = format.parse(endTime.replace("Z", " UTC"));
					format = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
					endTime = format.format(d);
				} catch (ParseException e) {
					e.printStackTrace();
				}//注意是空格+UTC
				jpql +=" and o.created_time < '"+endTime+"'";
			}
		}else if(type == 1){
			//周
			jpql +=" and o.created_time >= TIMESTAMP(DATE(DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -1 WEEK)))";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 2){
			//月
			jpql +=" and o.created_time >= DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -1 MONTH)";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 3){
			//季度
			jpql +=" and o.created_time >= TIMESTAMP(DATE(DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -1 MONTH)))";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}else if(type == 4){
			//年
			jpql +=" and o.created_time >= TIMESTAMP(DATE(DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 DAY),INTERVAL -3 MONTH)))";
			jpql +=" and o.created_time < TIMESTAMP(DATE(NOW()))";
		}
		if(province != null && !province.equals("")){
			jpql += " and s.province like '%"+province+"%'";
		}
		if(city != null && !city.equals("")){
			jpql += " and s.city like '%"+city+"%'";
		}
		if(area != null && !area.equals("")){
			jpql += " and s.area like '%"+area+"%'";
		}
		jpql += " GROUP BY o.store_id"
				+ " ORDER BY s.createtime desc"
				+ " ) a";
		Query query = entityManager.createNativeQuery(jpql);
		total = (BigDecimal)query.getSingleResult();
		return total.intValue();
	}
	
	/**
	 * 根据公司id和筛选条件,查询消费记录(状态为:用户主动支付订单)(若page和size都为-1,则为全部查找,且不考虑筛选条件;否则为分页查找,考虑筛选条件)
	 * @param page 请求第几页  从0开始   
	 * @param size 每页显示数量
	 * @param userNameForShow 筛选条件可选  用户名称  规则:若为已认证,则显示真实姓名,否则显示昵称
	 * @param departmentName 筛选条件可选  部门名称
	 * @param beginTime 筛选条件可选  会议开始时间
	 * @param endTime 筛选条件可选  会议结束时间
	 * @param enterpriseId 公司id
	 * @return orderItemList:匹配筛选条件的消费记录集合   total:匹配筛选条件的全部消费记录数量 
	 */
	public Map<String, Object> findOrderItemByEnterpriseId(Integer page, Integer size, String userNameForShow, String departmentName, String beginTime, String endTime, String enterpriseId){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select o.id,"
				+ " o.begin_time,"
				+ " case (select u.is_check from user u where u.id=o.user_id)"
				+ " when 0 then (select u.nickname from user u where u.id=o.user_id)"
				+ " when 1 then (select u.real_name from user u where u.id=o.user_id)"
				+ " end as user_name_for_show,"
				+ " (select d.name from department d where d.id=o.department_id) as department_name,"
				+ " (select s.store_name from stores s where s.id=o.store_id) as store_name,"
				+ " o.amount,"
				+ " o.business_card_amount,"
				+ " o.pay_amount,"
				+ " TIMESTAMPDIFF(MICROSECOND, o.begin_time, o.end_time) as spend_time"
				+ " from orders o"
				+ " where o.status='8'"
				+ " and o.business_card_amount>0"
				+ " and o.enterprise_id=:enterpriseId";
		
		//拼接筛选条件(若page和size都为-1,则不拼接筛选条件)
		if(page != -1 && size != -1){
			if(userNameForShow != null && userNameForShow.length() != 0){
				jpql += " and o.user_id in (select u.id"
										+ " from user u"
										+ " where (is_check=1 and real_name like :userNameForShow)"
										+ " or (is_check=0 and nickname like :userNameForShow)"
										+ " )";
			}
			if(departmentName != null && departmentName.length() != 0){
				jpql += " and o.department_id in (select d.id"
												+ " from department d"
												+ " where d.name like :departmentName)";
			}
			if(beginTime != null && beginTime.length() != 0 && endTime != null && endTime.length() != 0){
				jpql += " and o.begin_time >= :beginTime and o.end_time <= :endTime";
			}
		}
		
		//设置排序规则   按开始时间降序排序
		jpql += " order by o.begin_time desc";
		
		//查询符合查询的总数量
		Query query = entityManager.createNativeQuery(jpql, OrderItemVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		if(page != -1 && size != -1){
			if(userNameForShow != null && userNameForShow.length() != 0){
				query.setParameter("userNameForShow", "%" + userNameForShow + "%");
			}
			if(departmentName != null && departmentName.length() != 0){
				query.setParameter("departmentName", "%" + departmentName + "%");
			}
			if(beginTime != null && beginTime.length() != 0 && endTime != null && endTime.length() != 0){
				query.setParameter("beginTime", beginTime);
				query.setParameter("endTime", endTime);
			}
		}
		map.put("total", query.getResultList().size());

		//查询符合查询的实体集合
		//设置分页(若page和size都为-1,则不设置分页)
		if(page != -1 && size != -1){
			jpql += " limit " +  page*size + ", " +size;
		}
		Query query1 = entityManager.createNativeQuery(jpql, OrderItemVO.class);
		query1.setParameter("enterpriseId", enterpriseId);
		if(page != -1 && size != -1){
			if(userNameForShow != null && userNameForShow.length() != 0){
				query1.setParameter("userNameForShow", "%" + userNameForShow + "%");
			}
			if(departmentName != null && departmentName.length() != 0){
				query1.setParameter("departmentName", "%" + departmentName + "%");
			}
			if(beginTime != null && beginTime.length() != 0 && endTime != null && endTime.length() != 0){
				query1.setParameter("beginTime", beginTime);
				query1.setParameter("endTime", endTime);
			}
		}
		map.put("orderItemList", query1.getResultList());
		
		return map;
	}
	
	/**
	 * 门店名称是否存在
	 */
	public boolean hasName(String id,String name){
		boolean flag = false;
		String jpql = "select case when (count(id) > 0) then 'true' else 'false' end as hasName from stores"
				+ " where 1=1 AND is_use <> 1 AND store_name = :storeName";
		if(id != null && !id.equals("")){
			jpql += " and id <> :id";
		}
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("storeName", name);
		if(id != null && !id.equals("")){
			query.setParameter("id", id);
		}
		flag = Boolean.valueOf(query.getSingleResult().toString());
		return flag;
	}
	
}
